"""
# @Software: PyCharm
# @Author : zhoudeshui base wsf mysql_tool
"""
import logging
import warnings
import dmPython

warnings.simplefilter("ignore")
_logger = logging.getLogger(__name__)
__author__ = 'zhoudeshui base wsf mysql_tool'
__version__ = '0.0.1'


class my_dmsql():
    driver = dmPython
    """The my_dmsql is a tool for dm connect."""

    def __init__(self, host, user, password, port=5236):
        self.host = host
        self.user = user
        self.password = password
        self.port = port

    def _connect(self):
        try:
            return self.driver.connect(user=self.user, password=self.password, server=self.host, port=self.port)
        except AttributeError:
            _logger.error("No connect method found in self.driver module")

    def my_fetchall(self, sql, args=None, return_type=None):
        """
        Tested and usable by zhoudeshui
        :param sql:
        :param args:
        :param return_type:
        :return:
        """
        rows = []
        try:
            con = self._connect()
            cur = con.cursor()

            try:
                if ";" not in sql:
                    _logger.warning('dm_tool.my_fetchall: sql not end with \";\";')

                cur.execute(sql, args)

                # 获取列名（这里也可以移除，因为非 "dict" 类型不需要）
                # column_names = [desc[0] for desc in cur.description]

                rows = cur.fetchall()
                if return_type == "dict":
                    # 只有在 return_type 为 "dict" 时才进行字典转换
                    column_names = [desc[0] for desc in cur.description]
                    rows = [{column_name: value for column_name, value in zip(column_names, row)} for row in rows]
                else:
                    rows = [r for r in rows]

                _logger.debug("dm_tool.my_fetchall sql is %s" % cur.statement)
            except self.driver.Error as error:
                _logger.error('dm_tool.my_fetchall: sql:' + sql + ';' + str(error))
            finally:
                con.close()
        except self.driver.Error as error:
            _logger.error('dm_tool.my_fetchall: default.cnf error:' + str(error))

        if str(rows) == "[(None,)]":
            rows = []

        return rows

    def my_fetchone(self, sql, args=None, return_type=None):
        """
        Tested and usable by zhoudeshui
        :param sql:
        :param args:
        :param return_type:
        :return:
        """
        row = []
        column_names = []
        try:
            con = self._connect()
            cur = con.cursor()
            try:
                if ";" not in sql:
                    _logger.warning('dm_tool.my_fetchone: sql not end with \";\";')

                cur.execute(sql, args)

                # 获取列名（这里也可以移除，因为非 "dict" 类型不需要）
                # column_names = [desc[0] for desc in cur.description]

                row = cur.fetchone()
                if row is None:
                    row = {}
                elif return_type == "dict":
                    # 只有在 return_type 为 "dict" 时才进行字典转换
                    column_names = [desc[0] for desc in cur.description]
                    row = dict(zip(column_names, row))

                _logger.info("my_fetchone sql is %s" % cur.statement)
            except self.driver.Error as error:
                _logger.error("dm_tool.my_fetchone: sql = %s ; error= %s" % (cur._last_executed, str(error)))
                raise error
            finally:
                con.close()
        except self.driver.Error as error:
            _logger.error('dm_tool.my_fetchone: default.cnf error:' + str(error))

        # 对于空行或只有一个 None 的情况
        if not row or (isinstance(row, list) and len(row) == 1 and row[0] is None):
            row = {}

        # 如果返回类型不是 "dict"，则无需处理为字典
        if return_type != "dict" and isinstance(row, dict):
            row = list(row.values())

        return row

    def exe_update(self, table, colomns, conditions):
        """
        :param table: 表名
        :param colomns: 更新列
        :param conditions: 条件
        :return:
        """
        handled_item = 0
        sql = "update {} set ".format(table)
        try:
            con = self._connect()
            cur = con.cursor()
            try:
                cols = []
                vals = []
                for k, v in colomns.items():
                    if v is not None:
                        if v == "now()":
                            col = "{}=now()".format(k)
                            cols.append(col)
                        else:
                            col = "{}=%s".format(k)
                            cols.append(col)
                            vals.append(v)
                cons = []
                for k, v in conditions.items():
                    wheres = "{}=%s".format(k)
                    cons.append(wheres)
                    vals.append(v)

                sql = "{} {} where {};".format(sql, ",".join(cols), ",".join(cons))
                handled_item = cur.execute(sql, vals)
                _logger.debug("execute sql is %s" % cur.statement)
            except self.driver.Error as error:
                _logger.error('dm_tool.execute except: sql:' + sql + ';\n' + str(error))
                return -1
            except Exception as error:
                _logger.error("exe_update error: sql = %s ; error= %s" % (cur.statement, str(error)))
            finally:
                con.commit()
                con.close()
        except Exception as error:
            _logger.error('dm_tool.execute: default.cnf error:' + sql + ';' + str(error))
            return -1
        return handled_item

    def execute(self, sql, args=None):
        """
        Tested and usable by zhoudeshui
        :return:
        """
        handled_item = 0
        try:
            con = self._connect()
            cur = con.cursor()
            try:
                if ";" not in sql:
                    _logger.warning('dm_tool.execute: sql not end with \";\";')
                handled_item = cur.execute(sql, args)
                _logger.error("execute sql is %s" % cur.statement)
            except self.driver.Error as error:
                _logger.error('dm_tool.execute except: sql:' + sql + '\n' + str(error))
                return -1
            except Exception as error:
                _logger.error('dm_tool.execute except: sql:' + sql + '\n' + str(error))
            finally:
                con.commit()
                con.close()
        except Exception as error:
            _logger.error('dm_tool.execute: default.cnf error:' + sql + str(error))
            return -1
        return handled_item

    def insert(self, sql, args=None):
        """
        insert action
        :return:
        """
        handled_item = 0
        try:
            con = self._connect()
            cur = con.cursor()
            try:
                cur.execute(sql, args)
                handled_item = cur.lastrowid
                _logger.info("insert sql is %s" % cur.statement)
            except self.driver.Error as error:
                _logger.error('dm_tool.execute: sql:' + sql + str(error))
            finally:
                con.commit()
                con.close()
        except self.driver.Error as error:
            _logger.error('dm_tool.execute: default.cnf error:' + str(error))
        return handled_item

    def execute_transaction(self, array_sql_action, args=None):
        """

        :param array_sql_action:
            The format is a single SQL, or list ﹣ SQL
        All the list SQL executed successfully, return len (list_sql)
        :return:
        """
        handled_item = 0
        try:
            con = self._connect()
            cur = con.cursor()
            sql = ""
            try:
                con.autocommit(False)
                if type(array_sql_action) is list:
                    i = 1
                    for item in array_sql_action:
                        sql = item
                        if ";" not in sql:
                            _logger.warning('dm_tool.execute_transaction: sql not end with \";\";')
                        if args is not None:
                            handled_item = handled_item + cur.execute(sql, args[i - 1])
                        else:
                            handled_item = handled_item + cur.execute(sql)
                        _logger.info("execute_transaction sql %s is %s" % (i, cur.statement))
                        i = i + 1
                else:
                    handled_item = cur.execute(array_sql_action)
            except self.driver.Error as error:
                handled_item = -1
                con.rollback()
                _logger.error('dm_tool.execute_transaction: sql: %s : %s' % (sql, str(error)))
            finally:
                con.commit()
                con.close()
        except self.driver.Error as error:
            _logger.error('dm_tool.execute_transaction: default.cnf error:' + str(error))
        return handled_item
